package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import dto.SubjectHasStudentDto;

public class SubjectHasStudentDao {

	public String insertSubjectHasStudent(SubjectHasStudentDto subjectHasStudent) {

		if (subjectHasStudent == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO subject_has_student "
				+ "(subject_subject_id, student_user_id) " + "VALUES (?,?)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, subjectHasStudent.getSubjectId());
			preparedStatement.setString(2, subjectHasStudent.getStudentId());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteSubjectHasStudent(String subjectId, String studentId) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM subject_has_student WHERE subject_subject_id = ? and student_user_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, subjectId);
			preparedStatement.setString(2, studentId);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public ArrayList<SubjectHasStudentDto> getAllSubjectHasStudent() {

		ArrayList<SubjectHasStudentDto> listSubjectHasStudent = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM subject_has_student";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				SubjectHasStudentDto subjectHasStudent = new SubjectHasStudentDto();
				subjectHasStudent.setSubjectId(resultSet
						.getString("subject_subject_id"));
				subjectHasStudent.setStudentId(resultSet
						.getString("student_user_id"));
				listSubjectHasStudent.add(subjectHasStudent);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listSubjectHasStudent;
	}

}
